package com.excel;

import java.io.File;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.ConfigManager;
import com.bean.BaseModel;
import com.bean.JavaBeanParse;

/**
 * 
 * @author zhoufang
 * @version v0.1 2019年7月8日 下午9:09:37  zhoufang
 */
public class ExcelParse {

	// 黄色忽略, 客户端读取
	public static final int YELLOW = 13;
	// 第2行为描述
	public static final int DESC_FIRST = 1;
	// 第3行为名称
	public static final int NAME_TWO = 2;
	// 第4行为类型
	public static final int TYPE_THREE = 3;

	public static Workbook loadExcel(String excelPath) {
		try {
			File file = new File(excelPath);
			return WorkbookFactory.create(file);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 解析excel
	 * @param wb
	 * @param sheetIndex  sheet页数
	 * @param readFieldValues 是否需要读取字段值
	 * @return
	 */
	public static ExcelData parseExcelSheet(Workbook wb, int sheetIndex, boolean readFieldValues) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		Row firstRow = sheet.getRow(0);
		// 总行数
		int totalRow = (int) Double.parseDouble(getCellValue(firstRow.getCell(0)));
		// 总列数
		int totalCell = (int) Double.parseDouble(getCellValue(firstRow.getCell(1)));
		ExcelData data = new ExcelData();
		data.sheetName = sheet.getSheetName();
		// 第2行为字段描述, 第3行为字段名 , 第4行为字段类型, 第5行以后为字段的值
		int realRow = 1;
		for (int rowI = DESC_FIRST; rowI < totalRow; rowI++) {
			if (rowI > TYPE_THREE && !readFieldValues) {
				// 不读取字段值
				break;
			}
			// 本行数据
			Row row = sheet.getRow(rowI);
			if (row == null) {
				break;
			}
			Map<Integer, String> fieldValue = new HashMap<Integer, String>();
			for (int cellI = 0; cellI < totalCell; cellI++) {
				Cell cell = row.getCell(cellI);
				if (cell == null) {
					continue;
				}
				if (rowI == DESC_FIRST) {
					// 第2行读取颜色
					short color = cell.getCellStyle().getFillBackgroundColor();
					if (color == YELLOW) {
						data.ignoreIndex.add(cellI);
					}
				}
				String cellValue = getCellValue(cell);
				if (cellValue != null) {
					fieldValue.put(cellI, cellValue);
				}
			}
			// 读取完毕
			if (fieldValue.size() > 0) {
				data.fieldValues.add(fieldValue);
				realRow++;
			}else{
				System.out.println("实际行数:"+realRow);
				break;
			}
		}
		return data;
	}
	
	/**
	 * excel文件生成Javabean
	 * @param excelName
	 */
	public static void generateJavaBean(String excelName) {
		if (!excelName.endsWith(".xlsx") && !excelName.endsWith(".xls")) {
			return;
		}
		String excelPath = ExcelPath.getExcelBasePath() + excelName;
		Workbook wb = loadExcel(excelPath);
		if (wb == null) {
			return;
		}
		ExcelData excelData = parseExcelSheet(wb, 0, false);
		// 生成javabean
		JavaBeanParse.generateJava(excelName, excelData);
	}
	
	/**
	 * 给class赋值
	 * @param clazz
	 * @throws Exception
	 */
	public static <T extends BaseModel> void endueValue(Class<T> clazz) throws Exception {
		String excelPath = ExcelPath.getExcelPath(clazz);
		if (excelPath == null) {
			return;
		}
		Workbook wb = loadExcel(excelPath);
		if (wb == null) {
			return;
		}
		ExcelData excelData = parseExcelSheet(wb, 0, true);
		for (int i = TYPE_THREE; i < excelData.fieldValues.size(); i++) {
			Map<Integer, String> fieldValueMap = excelData.fieldValues.get(i);
			T t = clazz.newInstance();
			for (Entry<Integer, String> entry : fieldValueMap.entrySet()) {
				if (excelData.ignoreIndex.contains(entry.getKey())) {
					continue;
				}
				String fieldName = excelData.getNameMap().get(entry.getKey());
				t.setFieldValue(fieldName, entry.getValue());
			}
			ConfigManager.addConfigModel(t);
		}
	}

//	private static Set<Integer> getIgnoreIndex(Sheet sheet) {
//		Set<Integer> ignoreIndex = new HashSet<Integer>();
//		int firstRowNum = sheet.getFirstRowNum();
//		Row row = sheet.getRow(firstRowNum);
//		short firstCellNum = row.getFirstCellNum();
//		short lastCellNum = row.getLastCellNum();
//		for (int cellI = firstCellNum; cellI < lastCellNum; cellI++) {
//			Cell cell = row.getCell(cellI);
//			short color = cell.getCellStyle().getFillBackgroundColor();
//			if (color == YELLOW) {
//				ignoreIndex.add(cellI);
//			}
//		}
//		return ignoreIndex;
//	}

	private static String getCellValue(Cell cell) {
		CellType cellTypeEnum = cell.getCellTypeEnum();
		String value = null;
		if (cellTypeEnum == CellType.NUMERIC) {
			value = Double.toString(cell.getNumericCellValue());
		} else if (cellTypeEnum == CellType.STRING) {
			value = cell.getStringCellValue();
		} else if (cellTypeEnum == CellType.BOOLEAN) {
			value = Boolean.toString(cell.getBooleanCellValue());
		} else {
			// 未知类型
		}
		return value;
	}

}
